﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class ApiOptionChoiceRepository : BaseRepository<OptionChoice>
    {
        protected override string TableName
        {
            get { return "[OptionChoice]"; }
        }

        public IEnumerable<OptionChoice> Find(int? pkgDestId, int? optionId)
        {
            string strSQL = @"  select och.ChoiceId, och.PkgDestId, och.ChoiceDescription,
                                    vo.OptionId, vo.OptionName,
                    convert(bit, case 
                                    when exists(select 1 from ClientPackageChoice (nolock) as cpch where och.ChoiceId = cpch.ChoiceId) then 0
                                else 1 end) as CanDelete
                    from [OptionChoice] (nolock) as och left join [VacationOption] (nolock) as vo on och.OptionId = vo.OptionId ";
            
            string sWhere = "";
            if (pkgDestId.HasValue)
            {
                if (!string.IsNullOrEmpty(sWhere))
                    sWhere += " and ";
                sWhere += string.Format("(och.PkgDestId = {0})", pkgDestId.Value.ToString());
            }
            if (optionId.HasValue)
            {
                if (!string.IsNullOrEmpty(sWhere))
                    sWhere += " and ";
                sWhere += string.Format("(och.OptionId = {0})", optionId.Value.ToString());
            }
            if (!string.IsNullOrEmpty(sWhere))
                strSQL += "where " + sWhere;

            strSQL += " order by och.PkgDestId, vo.OptionName";

            IEnumerable<OptionChoice> result = Connection.Query<OptionChoice>(strSQL);
            return result;
        }

        public override OptionChoice Find(int id)
        {
            OptionChoice result = Connection.Query<OptionChoice>(
                @"  select och.ChoiceId, och.PkgDestId, och.OptionId, och.ChoiceDescription
                    from [OptionChoice] (nolock) as och
                    where och.ChoiceId = @ChoiceId", new { ChoiceId = id }).SingleOrDefault();
            return result;
        }

        public override void Insert(OptionChoice item)
        {
            Connection.Execute("spOptionChoice_Add @PkgDestId = @PkgDestId, @OptionId = @OptionId, @ChoiceDescription = @ChoiceDescription", item);
        }

        public override void Update(OptionChoice item)
        {
            Connection.Execute("spOptionChoice_Upd @ChoiceId = @ChoiceId, @PkgDestId = @PkgDestId, @OptionId = @OptionId, @ChoiceDescription = @ChoiceDescription", item);
        }

        public new string Delete(int id)
        {
            return Connection.Query<string>("spOptionChoice_Delete @ChoiceId = @ChoiceId", new { ChoiceId = id }).Single();
        }
        
    }
}
